

22	20019133	A111	房世通	213115	转子装配                                          	2020-02-01	星期六	70000010	NULL	0	0	400	0	0	0	0	0	0	0	0	0	0	0	0	0	NULL	0	0	0	0	0	0	0	0	0	NULL	NULL	0	0	0	NULL	NULL	NULL	NULL	NULL	NULL	0	0
23	20092227	20092227	张五	200004	一卡通管理部门	2020-02-11	星期二	70000011	NULL	0	0	0	0	100	0	0	0	0	0	0	0	0	0	0	0	NULL	0	0	0	0	0	0	0	0	0	NULL	NULL	0	0	0	NULL	NULL	NULL	NULL	NULL	NULL	0	0
24	20092362	9822	李小杰	212893	起动机D6G总装－1                                  	2020-02-11	星期二	70000013	NULL	0	0	30	0	0	0	0	0	0	0	0	0	0	0	0	0	NULL	0	0	0	0	0	0	0	0	0	NULL	NULL	0	0	0	NULL	NULL	NULL	NULL	NULL	NULL	0	0
25	20092363	9828	许须文	212867	发电机总装－4                                     	2020-02-11	星期二	70000019	NULL	0	0	0	0	500	0	0	0	0	0	0	0	0	0	0	0	NULL	0	0	0	0	0	0	0	0	0	NULL	NULL	0	0	0	NULL	NULL	NULL	NULL	NULL	NULL	0	0


update bd_kq_timecard set shift_id = 70000019 where id = 25

select TOP 100 * from bd_kq_timecard


exec bd_get_data '2020-03-01','2020-03-10','212740,212741,212742,212743,212744,212745,212746,212747,212748,212749,212750,212751,212752,212753,212754,212755','',0,100,100


select * from bd_sys_proc_desc where item_class_id = '21'


exec bd_proc_team_get_data '2020-01-01','2020-04-10','212867,212893,200004,213115,212740,212741,212742,212743,212744,212745,212746,212747,212748,212749,212750,212751,212752,212753,212754,212755'


declare @startDate date = '2020-03-01';
declare @endDate date = '2020-04-10';
declare @deptIds varchar(max) = '212740,212741,212742,212743,212744,212745,212746,212747,212748,212749,212750,212751,212752,212753,212754,212755';


select *  from (select distinct ltrim(str(shift_id)) as shift_id,mc,bh from bd_kq_timecard t INNER JOIN zt_banci z on t.shift_id = z.id where convert(varchar(10),card_date) BETWEEN @startDate and @endDate and dept_id in(select v.value from string_split(@deptIds,',')v) and t.shift_id>0) as t

					insert into #data(dept_id,shift_id,card_date,swipe_begin,swipe_end)

					select dept_id, shift_id, card_date, swipe_begin, swipe_end
					from bd_kq_timecard a
					inner join string_split('212740,212741,212742,212743,212744,212745,212746,212747,212748,212749,212750,212751,212752,212753,212754,212755',',') b on a.dept_id = b.value
					where isnull(shift_id,0) > 0 and card_date >= '2020-03-01' and card_date <= '2020-04-10'

select c.dep_no_add,c.dep_name,a.card_date,a.shift_id,count(0)
from bd_kq_timecard a
inner join string_split(@dep_serial,',') b on a.dept_id = b.value
left join view_bd_dep_level c on b.value = c.dep_serial
where a.card_date >= @begin_date and a.card_date <= @end_date
group by c.dep_no_add,c.dep_name,a.card_date,a.shift_id



select * from view_bd_dep_level
select top 10 * from bd_kq_timecard


select * from zt_banci









SELECT p.*
FROM (
  SELECT dept_id, shift_id, card_date,0 as in_or_out, COUNT(swipe_begin) qty FROM bd_kq_timecard
  WHERE card_date>='2020-01-01' AND shift_id > 0 AND swipe_begin > 0
  GROUP BY dept_id, shift_id, card_date
) t
PIVOT (
	SUM(qty)
	FOR shift_id IN ([70000010], [70000011], [70000012], [70000013],[70000014],[70000015],[70000016],[70000017],[70000019])
) p
order by card_date desc

 union all

SELECT p.*
FROM (
	SELECT dept_id, shift_id, card_date,1 as in_or_out, COUNT(swipe_end) qty FROM bd_kq_timecard
	WHERE card_date>='2020-03-01' AND shift_id > 0 AND swipe_end > 0
	GROUP BY dept_id, shift_id, card_date
) t
PIVOT (
	SUM(qty)
	FOR shift_id IN ([1091], [1092], [2090], [2104])
) p




select * from bd_kq_timecard


inner join dt_dep d on b.value = d.dep_serial
inner join (
	select distinct dep_no_add,dep_name from view_bd_dep_level a
	inner join string_split(@dep_serial,',') b on a.dep_serial = b.value
) e on d.dep_no_add = e.dep_no_add


select *
from dt_dep a
inner join (
	select distinct dep_no_add,dep_name from view_bd_dep_level a
	inner join string_split(@dep_serial,',') b on a.dep_serial = b.value
) b on a.dep_no_add = b.dep_no_add




where dep_serial in ('212896','212896')



select * from bd_sys_proc_desc where item_class_id = '19'

1415	21	15	合计人数		0	NULL		1	NULL	100	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
1414	21	14	其他班次		0	NULL		1	NULL	100	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
1413	21	13	中夜班		0	NULL		1	NULL	100	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
1412	21	12	中班		0	NULL		1	NULL	100	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
1411	21	11	大夜班		0	NULL		1	NULL	100	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
1410	21	10	夜班		0	NULL		1	NULL	100	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
1409	21	9	日班		0	NULL		1	NULL	100	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
1408	21	8	大日班		0	NULL		1	NULL	100	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
1407	21	7	早班		0	NULL		1	NULL	100	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
1406	21	6	新早班(2班制)		0	NULL		1	NULL	150	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
1405	21	5	新夜班(2班制)		0	NULL		1	NULL	150	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
1404	21	4	出/入厂		0	NULL		1	NULL	100	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
1403	21	3	刷卡日期		0	NULL		1	NULL	100	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
1402	21	2	小组/生产流水线名称		0	NULL		1	NULL	150	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
1401	21	1	小组/生产流水线代号		0	NULL		1	NULL	150	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL

update bd_sys_proc_desc set column_name = 'teamNo' where id = '1401'

1415	21	99	合计人数
1404	21	4	出/入厂
1403	21	3	刷卡日期
1402	21	2	小组/生产流水线名称
1401	21	1	小组/生产流水线代号



-- 考勤打卡
-- swipe_begin	上班打卡
-- swipe_end 		下班打卡

select top 100 * from bd_kq_timecard


-- pivot 行转列


select * from view_bd_dep_level

M7043
select * from dt_dep where dep_name = '起动机D7R总装'

		select distinct a.bh as id,a.mc as name
        from zt_banci a
        inner join bd_banci_department_config b on a.id = b.banci_id
		inner join zt_shid c on a.bh = c.parent
        where c.lx = 0 and b.whether_deleted = 0

		select * from zt_banci

		select * from zt_shid
			
select * from bd_over_time_order order by create_time desc

select * from lr_jiaba where order_id = '1659'

exec bd_proc_ot_detail_del '1658','20095490','192.168.2.2','123456'

select * from bd_kq_timecard where user_Serial in ('20095490','') and card_date = '2020-03-13'

select * from a

select * from aa



update aa set aa.name2 = a.duration
from aa 
inner join a on aa.name = a.name

select * from dt_Dep where dep_serial = '200003'

select * from dt_user where user_no = 'G2121'

select * from dt_dep 

select * from zt_banci

select * from zt_glbc



select * from zt_shid
select * from bd_kq_accounts


select * from bd_enclosure
delete from zt_glbc where bh not in(26,28)

update zt_glbc set lx= 1 where bh = '16'

select * from kt_paiba where user_serial = '20092161'

select * from bd_kq_schedule_detail where user_serial = '20092161'

select top 110 * from timecard

exec bd_proc_over_time_order_detail_delete '[{"orderId":"1492","userSerial":"20022396"}]','127.0.0.1','123456'


select * from lr_jiaba where order_id = '1654'

select * from lr_jiaba where jbrq = '2020-03-03' and user_Serial = '20092161'


select * from dt_user where user_no = '8431'

select * from bd_over_time_order where order_no = '1000672'
20094819
20095490

/*
txcount �ܵ���
txtx ����ʣ��
*/
-- Ա�����ڱ�
select top 10 * from bd_nianjia where user_serial in ('20094819','20095490')
20094819	2400	0	0	0	2400	0	120	4800	0	0	0
20095490	7200	0	120	0	7200	0	120	14400	120	0	0

/*
ot_to_rest_hours �Ӱ�ת����
����Ϊ0
*/
-- ���ڿ�

select * from bd_kq_timecard where user_Serial in ('20094819','20095490') and card_date = '2020-03-13'
1238073	20094819	6620	����	212752	���չ滮                                          	2020-03-09	����һ	2090	�հ�8h	480	990	0	0	0	0	480	0	0	6	0	0	0	1546	1	1	ƽʱ�Ӱ�	900	1020	120	0	0	0	0	0	0	NULL	NULL	0	0	0	NULL	NULL	NULL	NULL	2020-03-09 07:00:00.000	2020-03-09 19:00:00.000	0	3
1240173	20095490	2713	������	212752	���չ滮                                          	2020-03-09	����һ	2090	�հ�8h	480	990	900	0	1020	0	480	90	0	6	0	0	0	1546	1	1	ƽʱ�Ӱ�	900	1020	120	0	120	0	0	0	120	NULL	NULL	0	0	0	NULL	NULL	NULL	NULL	2020-03-09 07:00:00.000	2020-03-09 19:00:00.000	1	3

/*
ho_type 2Ϊ����

ת���ݿ�������ȷ�ϣ�û������״̬����֪����Щ�Ӱ���ϸ��Ҫ��ȥ����
�Ӱ�5Сʱ ȷ��3Сʱ
������ʾ��
���Ҳ����ת����

�Ӱ�ת����
*/
-- ������ϸ��
select * from bd_kq_holidays where user_Serial in ('20094819','20095490') and ho_date = '2020-03-13'




				-- ��Ҫ�����ϰ�򿨵�����
				insert into kt_jl(sj,user_serial,fx,lx,whether_validity,mode,order_id)
				select getdate(),'123456',0,0,1,1,123


				select *from kt_jl where order_id = '1026'

				update dt_user set kqdkLx = '21' 

select * from bd_repair_attendance

select top 50 * from kt_jl order by sj desc

mode
	 0 ˢ��
	 1 ������		
	 
whether-validity 
	 0 ��Ч
	 1 ��Ч
	 	
select * from kt_jl

-- ������11
exec p_bd_sys_getMaxId 11 ;
exec p_bd_sys_getMaxNumber 11

select * from lr_jl

select * from bd_repair_attendance









------------------------------------------------------------------------------------------
truncate table lr_jiaba
truncate table bd_over_time_order

exec bd_proc_over_time_order_add '20052242','','2020-01-11 00:00:00','2020-01-11 00:01:00',3,1,'�Ӱ�Ӱ�','005','10001','127.0.0.1','super'


declare @aaa varchar(50);
select isnull('',null)


				select * from string_split('',',');

select * from bd_enclosure

truncate table lr_jiaba

select * from aaa
select * into aaa from kt_paiba

exec bd_proc_schedule_update_by_date '20052242','2020-01-04','42','192.168.2.2','super'


select * from kt_paiba

select * from bd_kq_schedule_detail
select * from kt_paiba


select * from lr_jiaba
select * from bd_over_time_order

update bd_over_time_order set status = 1 where order_id = 4

exec bd_proc_over_time_order_delete '1006','192.168.2.2','super'

exec bd_proc_over_time_order_add '20018818,20052242','2020-01-02 00:00:00','2020-01-03 00:01:00',3,1,'�Ӱ�Ӱ�','005','10001','127.0.0.1','super'


select count(a.user_serial) 
from lr_jiaba a inner join dt_user b on a.user_serial=b.user_serial 
where (b.dep_no is not null) and (b.user_type <= 50) and not(a.jssj < '2019-12-01 7:0:00' or '2019-12-01 6:0:00' > a.kssj ) and b.user_serial in ( 20052241 )

select count(a.user_serial) 
from lr_jiaba a inner join dt_user b on a.user_serial=b.user_serial
where (b.dep_no is not null) and (b.user_type <= 50) and (a.kssj >= '2019-12-01 2:0:00' and a.jssj <= '2019-12-02 3:0:00') and b.user_serial in ( 20052241 )
80

update [bd_sys_proc_desc] set width = 90 where width=80

300
301
310

select bh as id,name as name from bd_sys_item where type_id =1
/*

311	10	1	����	opr	0	NULL	bd_over_time_order	1	NULL	150	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	cellButton
--302	10	1	�Ӱ൥��	orderId	0	NULL	bd_over_time_order	1	NULL	90	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
--303	10	1	������	sumUser	0	NULL	bd_over_time_order	1	NULL	90	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
--304	10	1	��ʱ��	sumMinute	0	NULL	bd_over_time_order	1	NULL	90	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
--305	10	1	�Ӱ�����	typeName	0	NULL	bd_over_time_order	1	NULL	90	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
--306	10	1	�Ƿ�Ӳ�	isRepast	0	NULL	bd_over_time_order	1	NULL	90	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
--307	10	1	״̬	status	0	NULL	bd_over_time_order	1	NULL	90	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
--308	10	1	������	examineUser	0	NULL	bd_over_time_order	1	NULL	90	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
--309	10	1	�ύ��	createUser	0	NULL	bd_over_time_order	1	NULL	90	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
310	10	1	�ύʱ��	createTime	0	NULL	bd_over_time_order	1	NULL	200	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
--300	10	1	��ʼ����	beginDate	0	NULL	bd_over_time_order	1	NULL	100	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL
--301	10	1	��������	endDate	0	NULL	bd_over_time_order	1	NULL	100	NULL	NULL	NULL	center	1	NULL	0	1	0	0	0	0	NULL	NULL

*/
update [bd_sys_proc_desc] set column_name = 'glyNo' where id = 49


select * from [dbo].[bd_sys_proc_desc] where item_class_id = 6 order by item_class_id

select * from lr_jiaba	

kssj,jssj

select * from bd_sys_item

select * from lr_jiaba_20191225

select * from lr_kqingj

select a.id,order_id,begin_date,end_date,sum_user,sum_minute,a.type_id,b.name,reason,status,examine_user,create_user,create_time,case is_repast when 0 then '��' when 1 then '��' end as is_repast
from bd_over_time_order a
left join bd_sys_item b on a.type_id = b.id 


update bd_sys_proc_desc set column_name='sumMinute'  where id ='304'

select * from bd_over_time_order

select a.id,order_id,begin_date,end_date,sum_user,sum_minute,type_id,reason,status,examine_user,create_user,create_time,'����' as type_name,case is_repast when 0 then '��' when 1 then '��' end as is_repast
from bd_over_time_order a

select CONVERT(datetime,'2019-11-01 12:00:00',102)

select DATEADD(day,1,user_workday) from dt_user

select count(a.user_serial) 
from lr_jiaba a inner join dt_user b on a.user_serial=b.user_serial 
where (b.dep_no is not null) and (b.user_type <= 50) and not(a.kssj > '2019-12-24T16:00:00.000Z' or a.jssj < '2019-12-24T16:00:00.000Z') and b.user_serial in ( 20052241 )



/*

	�Ӱ� ��δ���ɼӰ൥

*/

insert into lr_jiaba(user_serial,bh,kssj,jssj,jsjg,bz,gly_no,shenhe,jbrq) 
select b.user_serial,'0' as bh,'2019-12-25 08:00:00' as kssj,'2019-12-25 17:00:00' as jssj,540 as jsjg,''as bz,'super' as gly_no,'super' as shenhe,'2019-12-25' as jbrq 
from dt_user b where (b.dep_no is not null) and (b.user_type<=50)and (b.user_dep=200002 or b.user_dep=200003 or b.user_dep=200004)
select count(a.user_serial) from lr_jiaba a inner join dt_user b on a.user_serial=b.user_serial where (b.dep_no is not null) and (b.user_type <= 50) and not(a.kssj > '2019-12-01' or a.jssj < '2019-12-31') and b.user_serial in ( 20052241 )


select * from bd_over_time_order
select * from lr_jiaba where user_serial = '20018818'

truncate table lr_jiaba

select * into lr_jiaba_20191225 from lr_jiaba


select * from [dbo].[bd_sys_proc_desc] where item_class_id = 10
select * from lr_kqingj

select * from dt_user


 SELECT distinct
            u.user_no as userNo,
            u.user_lname as userName,
            d.dep_name as deptName
        FROM
            lr_jiaba l
            LEFT JOIN dt_user u ON l.user_serial = u.user_serial
            LEFT JOIN dt_dep d on d.dep_serial  = u.user_dep
        where l.order_id = #{orderId}


-- ִ���Ű�洢����
exec bd_proc_schedule_pu_tong '20052241','2019-12-01','2019-12-31','00104','192.168.2.2','super'




-- ��ѯ��α�����ʱ����
declare @ban_ci_name varchar(50);
select @ban_ci_name = '[A]'+bh FROM zt_banci t WHERE bh = '00104'
print @ban_ci_name


select * from zt_banci	--���
select * from zt_glbc	--���
select * from kt_paiba order by rq desc

select * from zt_glbc where bh = 14
select * from bd_sys_dim_date


declare @lx smallint = -1;
select lx from zt_glbc where bh = 14;


-- ���ɰ�����ͣ���
IF @lx = 1 BEGIN
	print 'lx=1'	
END
ELSE BEGIN
	print 'lx=else'
END

CREATE TABLE #ban_ci(
	id INT IDENTITY,
	name VARCHAR(20)
)

-- ��ְ�α�����ʱ����
INSERT INTO #ban_ci
SELECT v.value FROM zt_glbc t CROSS APPLY STRING_SPLIT(CAST(t.Valuse AS VARCHAR(MAX)), ',') v WHERE bh = 14


SELECT 'd'+CAST(d.date_day AS VARCHAR(2)) colName, b.name,d.date,CONVERT(VARCHAR(7),date,120) date_month
FROM bd_sys_dim_date d
LEFT JOIN #ban_ci b ON b.id=d.date_week
WHERE date >= '2019-12-01' AND date <= '2019-12-31'

select * from [dbo].[bd_sys_proc_desc] order by id 

update [dbo].[bd_sys_proc_desc] set width = '125' where id = 310

select * from [dbo].[bd_sys_proc_desc] where item_class_id = '5'

select * from 


---------------------------------�����Ű�----------------------------------------


select a.d6,a.user_serial,a.rq from kt_paiba a 
inner join dt_user b on a.user_serial=b.user_serial  
where (b.dep_no is not null) and (b.user_type<=50) 
and (((a.rq>='2019-12') and (a.rq<='2019-12')) or (a.rq is null)) 
and (b.user_dep=200002 or b.user_dep=200003 or b.user_dep=200004) and (((b.user_serial=20052241) and (a.rq='2019-12')))

exec sp_cursorfetch 180150007,16,1,1

insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,mrbc,gly_no,sj) 
values(20052241,convert(varchar(10),dateadd(day,5,'2019-12'+'-01'),120),1,6,1,0,0,'[A]00101','super','2019/12/24 9:34:11')

select convert(varchar(10),dateadd(day,5,'2019-12'+'-01'),120) 

select user_lname from dt_user where user_serial=20052241

insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)  
VALUES (13,0,'super�޸��Ե���2019-12-06�Ű�','super','2019/12/24 9:34:12','','127.0.0.1','')

exec sp_cursor 180150007,33,1,'kt_paiba','[A]00101'

---------------------------------------------------------------------------


select * from kt_paiba where user_serial = '20052242'

exec bd_proc_schedule_update '20052241','d3','AAAAA','2019-12','192.168.2.2','Cps'



update kt_paiba set d11 = '[A]00101' where user_serial = '20052242' and rq = '2019-12'

select * from kt_paiba
 where user_serial = '20052242' and rq = '2019-12'

select * from lr_waich
select * from lr_jiaba


select count(a.user_serial) 
from lr_waich a 
inner join dt_user b on a.user_serial=b.user_serial 
where (b.dep_no is not null) and (b.user_type<=50)and (b.user_dep=200002 or b.user_dep=200003 or b.user_dep=200004) and not(a.kssj>'2019-12-23 14:00:00' or a.jssj<'2019-12-23 09:00:00')

select count(a.user_serial) from lr_jiaba a inner join dt_user b on a.user_serial=b.user_serial where (b.dep_no is not null) and (b.user_type<=50)and (b.user_dep=200002 or b.user_dep=200003 or b.user_dep=200004) and not(a.kssj>'2019-12-23 14:00:00' or a.jssj<'2019-12-23 09:00:00')


select a.user_serial,a.user_name,a.rq
from view_bd_schedule a where user_serial = '20018818'

select a.user_serial,a.user_name,a.rq
from view_bd_schedule a  where rq >= '2019-11' and rq <= '2019-11' and a.user_name like '%%' or a.user_no like '%%'


select a.user_serial,a.user_name,a.rq
from view_bd_schedule a inner join #user_serial b on a.user_serial = b.user_serial 

select * from wt_log order by log_time desc


exec bd_proc_schedule_delete_find '20052241,20052242','200002,200003,200004','2019-11','2019-11','','127.0.0.1','super'


select getdate()

insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,gly_no,sj)  
select @user_serial,date,1,6,2,0,0,@operator,getdate()
from bd_sys_dim_date 
where CONVERT(varchar(7),date,120) = @year_month

delete  from kt_paiba where user_serial = @user_serial and rq = @year_month

declare @user_name varchar(200);
select @user_name = user_lname from dt_user where user_serial = @user_serial;

insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)
VALUES (13,0,@operator+'ɾ��'+@user_name+@year_month+'�Ű�',@operator,getdate(),'',@ip,'')







select * 
from bd_sys_dim_date
where date >= '2019-12-01' and date <= '2019-12-31'

declare @compute_result int =540;
declare @over_time_type int = 0;

declare @begin_time varchar(200) = '08:00:00';
declare @end_time varchar(200) = '14:00:00';

declare @ip varchar(200);
declare @operator varchar(200);

select a.user_lname,a.user_serial,date,convert(varchar(7),date,120)+' '+@begin_time as begin_date,convert(varchar(7),b.date,120)+' '+@end_time as end_date
into #data
from (select top 2 * from dt_user ) a , (select * from bd_sys_dim_date where date >= '2019-12-01' and date <= '2019-12-31') as b


insert into lr_jiaba(user_serial,bh,kssj,jssj,jsjg,bz,gly_no,shenhe,jbrq) 
select user_serial,@over_time_type as bh,begin_date as kssj,end_date as jssj,@compute_result as jsjg,'' as bz,@operator as gly_no,@operator as shehe,date as jbrq from #data

insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)  
select 10,1,@operator+'¼�뷽�� '+begin_date+'---'+end_date+'�Ӱ�',@operator,getdate(),'',@ip,'' from #data

insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,bh,kssj,jssj,jsjg,gly_no,sj) 
select user_serial,date,1,3,1,0,0,@over_time_type,begin_date,end_date,@compute_result,@operator,getdate() from #data

--

-- ��ʱ��Ӱ�
insert into lr_jiaba(user_serial,bh,kssj,jssj,jsjg,bz,gly_no,shenhe,jbrq) 
select b.user_serial,'0' as bh,'2019-12-19 08:00:00' as kssj,'2019-12-19 17:00:00' as jssj,540 as jsjg,''as bz,'super' as gly_no,'super' as shenhe,'2019-12-19' as jbrq from dt_user b 
where (b.dep_no is not null) and (b.user_type<=50)and (b.user_dep=200002 or b.user_dep=200003 or b.user_dep=200004)
--
insert into lr_jiaba(user_serial,bh,kssj,jssj,jsjg,bz,gly_no,shenhe,jbrq) 
select b.user_serial,'1' as bh,'2019-12-21 08:00:00' as kssj,'2019-12-21 17:00:00' as jssj,540 as jsjg,''as bz,'super' as gly_no,'super' as shenhe,'2019-12-21' as jbrq from dt_user b where (b.dep_no is not null) and (b.user_type<=50)and (b.user_dep=200002 or b.user_dep=200003 or b.user_dep=200004)
--
insert into lr_jiaba(user_serial,bh,kssj,jssj,jsjg,bz,gly_no,shenhe,jbrq) 
select b.user_serial,'2' as bh,'2019-12-22 08:00:00' as kssj,'2019-12-22 17:00:00' as jssj,540 as jsjg,''as bz,'super' as gly_no,'super' as shenhe,'2019-12-22' as jbrq from dt_user b where (b.dep_no is not null) and (b.user_type<=50)and (b.user_dep=200002 or b.user_dep=200003 or b.user_dep=200004)
--


insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)  
VALUES (10,1,'super¼�뷽�� 2019-12-19 08:00:00---2019-12-19 17:00:00�Ӱ�','super','2019/12/19 10:55:56','','127.0.0.1','')
--
insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)  
VALUES (10,1,'super¼�뷽�� 2019-12-21 08:00:00---2019-12-21 17:00:00�Ӱ�','super','2019/12/19 11:59:09','','127.0.0.1','')
--
insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)  
VALUES (10,1,'super¼�뷽�� 2019-12-22 08:00:00---2019-12-22 17:00:00�Ӱ�','super','2019/12/19 12:00:41','','127.0.0.1','')
--
insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)  
VALUES (10,1,'super¼�뷽�� 2019-12-22 08:00:00---2019-12-22 17:00:00�Ӱ�','super','2019/12/19 12:00:41','','127.0.0.1','')
--


insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,bh,kssj,jssj,jsjg,gly_no,sj) 
VALUES (20092178,convert(varchar(10),'2019-12-19',120),1,3,1,0,0,'0','2019-12-19 08:00:00','2019-12-19 17:00:00',540,'super','2019/12/19 10:55:56')
--
insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,bh,kssj,jssj,jsjg,gly_no,sj) 
VALUES (20092178,convert(varchar(10),'2019-12-21',120),1,3,1,0,0,'1','2019-12-21 08:00:00','2019-12-21 17:00:00',540,'super','2019/12/19 11:59:09')
--
insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,bh,kssj,jssj,jsjg,gly_no,sj) 
VALUES (20092178,convert(varchar(10),'2019-12-22',120),1,3,1,0,0,'2','2019-12-22 08:00:00','2019-12-22 17:00:00',540,'super','2019/12/19 12:00:41')
--




-- ����μӰ�
insert into lr_jiaba(user_serial,bh,kssj,jssj,jsjg,bz,gly_no,shenhe,jbrq) 
select b.user_serial,'0' as bh,'2019-12-20 16:00:00' as kssj,'2019-12-21' as jssj,480 as jsjg,''as bz,'super' as gly_no,'super' as shenhe,'2019-12-20' as jbrq 
from dt_user b where (b.dep_no is not null) and (b.user_type<=50)and (b.user_dep=200002 or b.user_dep=200003 or b.user_dep=200004)

	
insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)  
VALUES (10,1,'super¼�뷽�� 2019-12-20 16:00:00---2019-12-21�Ӱ�','super','2019/12/19 11:19:07','','127.0.0.1','')


insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,bh,kssj,jssj,jsjg,gly_no,sj) 
VALUES (20092178,convert(varchar(10),'2019-12-20',120),1,3,1,0,0,'0','2019-12-20 16:00:00','2019-12-21',480,'super','2019/12/19 11:19:07')




-- ɾ���Ӱ�
insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,bh,kssj,jssj,jsjg,gly_no,sj) 
select user_serial,convert(varchar(10),jbrq,120),1,3,2,0,0,bh,kssj,jssj,jsjg,'super','2019/12/19 10:51:12' 
from lr_jiaba 
where user_serial=20056962 and bh='0' and kssj='2019-12-14 08:00:00' 
and jssj='2019-12-14 17:00:00' and (shenhe <>'' or shenhe is not null ) 

select  user_lname from  dt_user where user_serial =20056962

insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)  
VALUES (10,2,'superɾ������''2019-12-14 08:00:00''---''2019-12-14 17:00:00'' �Ӱ��¼','super','2019/12/19 10:51:13','','127.0.0.1','')

delete  from lr_jiaba where user_serial=20056962 and bh=0 and kssj='2019-12-14 08:00:00' and jssj='2019-12-14 17:00:00'




-- ִ���Ű�洢����
exec bd_proc_schedule_gui_lv '20052241','2019-12-01','2019-12-31',14,'192.168.2.2','super'



select * from zt_banci	--��ͨ���
select * from zt_glbc	--���ɰ��
select * from kt_paiba where user_serial = '20052241' order by rq desc

select * 
from kt_paiba a
where a.rq = CONVERT(varchar(7),'2019-12-03',120) or a.rq = CONVERT(varchar(7),'2019-12-06',120)

select * from wt_log order by log_time desc

--31129
select * from kt_paiba

SELECT * FROM bd_sys_dim_date d where date > '2019-12-02' and date <= '2019-12-08'


CREATE TABLE #banci(
    id INT IDENTITY,
    name VARCHAR(20)
)
INSERT INTO #banci
SELECT v.value
FROM zt_glbc   t
    CROSS APPLY STRING_SPLIT(CAST(t.Valuse AS VARCHAR(MAX)), ',') v
WHERE bh=19

--SELECT * FROM bd_sys_dim_date
--  WHERE date_key>=20191201 AND date_key<=20191210
--
--SELECT * FROM #banci b


SELECT * FROM bd_sys_dim_date d
  LEFT JOIN #banci b ON b.id=d.date_week
  WHERE date_key>=20191201 AND date_key<=20191210


DROP TABLE #banci




select * from bd_sys_dim_date

select * from string_split('1,2,3',',')

select * from  split_str('1,2,3',',')

select * from lr_jiaba

------------------------------------------------------------------------

-- �Ӱ��ѯ
select a.user_serial,a.bh,a.kssj,a.jssj,b.user_no,b.user_lname,b.user_depname,a.jsjg,a.bz,a.gly_no,a.shenhe 
from lr_jiaba a,dt_user b where ((b.dep_no is 
not null) and (b.user_type<=50) and (a.user_serial=b.user_serial) and (a.kssj>='2019-12-01') and (a.jssj<='2019-12-31 23:59:59') 
and (b.user_dep=200007 or b.user_dep=202158 or b.user_dep=203338 or b.user_dep=203358)) 
order by b.user_no ASC,a.kssj ASC


-- ¼��Ӱ�

insert into lr_jiaba(user_serial,bh,kssj,jssj,jsjg,bz,gly_no,shenhe,jbrq) 
select b.user_serial,'0' as bh,'2019-12-11 08:00:00' as kssj,'2019-12-11 17:00:00' as jssj,540 as jsjg,''as bz,'super' as gly_no,'super' as shenhe,'2019-12-11' as jbrq from dt_user b where (b.dep_no is not null) and (b.user_type<=50)and (b.user_dep=200002 or b.user_dep=200003 or b.user_dep=200004)

select user_serial,user_lname from dt_user b where (b.dep_no is not null) and (b.user_type<=50)and (b.user_dep=200002 or b.user_dep=200003 or b.user_dep=200004)

insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)  
VALUES (10,1,'super¼������ 2019-12-11 08:00:00---2019-12-11 17:00:00�Ӱ�','super','2019/12/11 11:16:12','','127.0.0.1','')

insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)  
VALUES (10,1,'super¼���־�� 2019-12-11 08:00:00---2019-12-11 17:00:00�Ӱ�','super','2019/12/11 11:16:12','','127.0.0.1','')

insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)  
VALUES (10,1,'super¼���Ե��� 2019-12-11 08:00:00---2019-12-11 17:00:00�Ӱ�','super','2019/12/11 11:16:12','','127.0.0.1','')

insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,bh,kssj,jssj,jsjg,gly_no,sj) 
VALUES (20056962,convert(varchar(10),'2019-12-11',120),1,3,1,0,0,'0','2019-12-11 08:00:00','2019-12-11 17:00:00',540,'super','2019/12/11 11:16:12')

insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,bh,kssj,jssj,jsjg,gly_no,sj) 
VALUES (20052242,convert(varchar(10),'2019-12-11',120),1,3,1,0,0,'0','2019-12-11 08:00:00','2019-12-11 17:00:00',540,'super','2019/12/11 11:16:12')

insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,bh,kssj,jssj,jsjg,gly_no,sj) 
VALUES (20052241,convert(varchar(10),'2019-12-11',120),1,3,1,0,0,'0','2019-12-11 08:00:00','2019-12-11 17:00:00',540,'super','2019/12/11 11:16:12')


--------------------------------------------------------------------


select * from dt_user where user_serial = '20056962'

--
select lx,valuse from zt_glbc where bh=13 and (regserial is null or regserial='')

select user_serial from dt_user where (dep_no is not null) and (user_type<=50)and (user_serial=20056962)

insert into kt_paiba(user_serial,rq,gly_no,d1,d2,d3,d4,d5,d6,d7,d8,d9,d10,d11,d12,d13,d14,d15,d16,d17,d18,d19,d20,d21,d22,d23,d24,d25,d26,d27,d28,d29,d30,d31) 
VALUES (20056962,'2019-12','super','[X]','[A]00101','[A]00101','[A]00101','[A]00101','[A]00101','[A]0010b','[X]','[A]00101','[A]00101','[A]00101','[A]00101','[A]00101','[A]0010b','[X]','[A]00101','[A]00101','[A]00101','[A]00101','[A]00101','[A]0010b','[X]','[A]00101','[A]00101','[A]00101','[A]00101','[A]00101','[A]0010b','[X]','[A]00101','[A]00101')
 
insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)  
VALUES (13,1,'super¼������2019-12�Ű�','super','2019/12/9 18:04:00','','127.0.0.1','')

------------------------------------------------------------------

select * from zt_glbc

select lx,valuse from zt_glbc where bh=19 and (regserial is null or regserial='')

select user_serial from dt_user where (dep_no is not null) and (user_type<=50)and (user_dep=200002 or user_dep=200003 or user_dep=200004)

select count(user_serial) from kt_paiba where user_serial=20056962 and rq='2019-12'

select user_lname from  dt_user where user_serial=20056962

update kt_paiba set d1='[X]00101',d2='[A]00101',d3='[A]00101',d4='[A]00101',d5='[A]00101',d6='[A]00101',d7='[X]00101',d8='[X]00101',d9='[A]00101',d10='[A]00101',d11='[A]00101',d12='[A]00101',d13='[A]00101',d14='[X]00101',d15='[X]00101',d16='[A]00101',d17='[A]00101',d18='[A]00101',d19='[A]00101',d20='[A]00101',d21='[X]00101',d22='[X]00101',d23='[A]00101',d24='[A]00101',d25='[A]00101',d26='[A]00101',d27='[A]00101',d28='[X]00101',d29='[X]00101',d30='[A]00101',d31='[A]00101' where user_serial=20056962 and rq='2019-12'

insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)  VALUES (13,0,'super�޸�����2019-12�Ű�','super','2019/12/9 18:13:31','','127.0.0.1','')

--
select count(user_serial) from kt_paiba where user_serial=20052241 and rq='2019-12'
select user_lname from  dt_user where user_serial=20052241
insert into kt_paiba(user_serial,rq,gly_no,d1,d2,d3,d4,d5,d6,d7,d8,d9,d10,d11,d12,d13,d14,d15,d16,d17,d18,d19,d20,d21,d22,d23,d24,d25,d26,d27,d28,d29,d30,d31) VALUES (20052241,'2019-12','super','[X]00101','[A]00101','[A]00101','[A]00101','[A]00101','[A]00101','[X]00101','[X]00101','[A]00101','[A]00101','[A]00101','[A]00101','[A]00101','[X]00101','[X]00101','[A]00101','[A]00101','[A]00101','[A]00101','[A]00101','[X]00101','[X]00101','[A]00101','[A]00101','[A]00101','[A]00101','[A]00101','[X]00101','[X]00101','[A]00101','[A]00101')
insert into wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)  VALUES (13,1,'super¼���Ե���2019-12�Ű�','super','2019/12/9 18:13:31','','127.0.0.1','')




select count(b.user_serial) 
from kt_paiba a 
inner join dt_user b on a.user_serial=b.user_serial 
where ((b.dep_no is not null) and (b.user_type<=50) and (((a.rq>='2019-12') and (a.rq<='2019-12')) or (a.rq is null)) and 
(b.user_dep=201299 or b.user_dep=201300 or b.user_dep=201301 or b.user_dep=201302 or b.user_dep=201303 or b.user_dep=201304 or b.user_dep=201305 or b.user_dep=201306 or b.user_dep=201673 or b.user_dep=201674 or b.user_dep=201675 or b.user_dep=201676 or b.user_dep=201677 or b.user_dep=201678 or b.user_dep=201679 or b.user_dep=201680 or b.user_dep=201693 or b.user_dep=201694 or b.user_dep=201695 or b.user_dep=201696))


select * from [dbo].[bd_sys_proc_desc]

select TOP 10 * from zt_day order by dkrq desc

select * from zt_banci	--���
select * from zt_glbc	--���
select * from kt_paiba order by rq desc

